﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Odbc;
using MySql.Data.MySqlClient;


namespace WHM3
{
    public static class DAL
    {
        private static bool initiated = false;
        private static MySqlConnectionStringBuilder connectionString;
        private static MySqlConnection connection;


        public static void init()
        {
            if (initiated) { return; }

            connectionString = new MySqlConnectionStringBuilder();
            connectionString.Server = "db4free.net";
            connectionString.UserID = "whm1";
            connectionString.Password = "1@3$5^";
            connectionString.Database = "whm1";

            using (connection = new MySqlConnection(connectionString.ToString()))

                //openConnection();
                initiated = true;

            //itais server - doesnt work yet

            //if (debug) { return; }
            //if (initiated) { return; }

            //connectionString = new MySqlConnectionStringBuilder();
            //connectionString.Server = "mysql16.joinweb.co.il";
            //connectionString.UserID = "whosinco_igor";
            //connectionString.Password = "1@3$5^";
            //connectionString.Database = "whosinco_WHM";

            //using (connection = new MySqlConnection(connectionString.ToString()))

            //    //openConnection();
            //    initiated = true;
        }


        public static void addEmployee(Employee employee)
        {
            if (!initiated)
                init();

            string newCommand = "";
            newCommand += "INSERT INTO workers VALUES(";
            newCommand += "'" + employee.id + "', ";
            newCommand += "'" + employee.firstName + "', ";
            newCommand += "'" + employee.lastName + "', ";
            newCommand += "'" + employee.Password + "', ";
            newCommand += "'" + employee.Position + "', ";
            newCommand += "'" + employee.isManager + "', ";
            newCommand += "'" + employee.isAdmin + "', ";
            newCommand += "'" + employee.managerOf + "', ";
            newCommand += "'" + employee.Team + "', ";
            newCommand += "'" + employee.minHours + "');";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            addToChangeLog(new ChangeLogEntry(0,"Sys Admin","","Added the Employee: "+employee.id+" "+employee.firstName+" "+employee.lastName+" To Team: "+employee.Team));
        }

        public static Employee getEmployeeById(string id)
        {
            if (!initiated)
                init();

            string newCommand = "SELECT * FROM workers WHERE ID='" + id + "';";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            Employee employee = null;
            if (rdr.Read())
            {
                string firstName = (string)rdr["firstName"];
                string lastName = (string)rdr["lastName"];
                string Password = (string)rdr["Password"];
                string Position = (string)rdr["Position"];
                string isManager = (string)rdr["isManager"];
                string isAdmin = (string)rdr["isAdmin"];
                string managerOf = (string)rdr["managerOf"];
                string Team = (string)rdr["Team"];
                string minHours = (string)rdr["minHours"];
                
                employee = new Employee(id, firstName, lastName, Password, Position, isManager, isAdmin, managerOf, Team, minHours);

            }
           
            connection.Close();
            return employee;

        }

        public static LinkedList<Employee> GetAllEmployees()
        {
            if (!initiated)
                init();

            string newCommand = "SELECT * FROM Employees";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            LinkedList<Employee> list = new LinkedList<Employee>();

            while (rdr.Read())
            {
                string id = (string)rdr["id"];
                string firstName = (string)rdr["firstName"];
                string lastName = (string)rdr["lastName"];
                string Password = (string)rdr["Password"];
                string Position = (string)rdr["Position"];
                string isManager = (string)rdr["isManager"];
                string isAdmin = (string)rdr["isAdmin"];
                string managerOf = (string)rdr["managerOf"];
                string Team = (string)rdr["Team"];
                string minHours = (string)rdr["minHours"];

                list.AddFirst(new Employee(id, firstName, lastName, Password, Position, isManager, isAdmin, managerOf, Team,minHours));
            }
            connection.Close();
            return list;
        }

        public static void deleteEmployee(Employee e)
        {
            if (!initiated)
                init();
            //DELETE FROM `whm1`.`workers` WHERE `workers`.`ID` = '00'
            string newCommand = "DELETE FROM workers WHERE ID=" + e.id;

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            addToChangeLog(new ChangeLogEntry(0, "Sys Admin", "", "Deleted the Employee: " + e.id + " " + e.firstName + " " + e.lastName + " To Team: " + e.Team));
        }

        public static bool addShift(string id, string start, string end)
        {
            if (!initiated)
                init();

            string newCommand = "";
            newCommand += "INSERT INTO shifts VALUES(";
            newCommand += "'" + id + "', ";
            newCommand += "'" + start + "', ";
            newCommand += "'" + end + "')";
           
            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            addToChangeLog(new ChangeLogEntry(0, id, DAL.getEmployeeById(id).Team, "Added a shift: "+start+" to "+end));

            return true;

        }

        public static bool checkIntoWork(string id, string start)
        {
            if (!initiated)
                init();

            string newCommand = "";
            newCommand += "INSERT INTO shifts VALUES(";
            newCommand += "'" + id + "', ";
            newCommand += "'" + start + "', ";
            newCommand += "'')";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            addToChangeLog(new ChangeLogEntry(0, id, DAL.getEmployeeById(id).Team, id+" Has Entered Work at: "+start));

            return true;
        }

        public static LinkedList<Shift> getAllShiftsById(string ID)
        {


            if (!initiated)
                init();

            string newCommand = "SELECT * FROM shifts WHERE ID=\'" + ID + "\'";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            LinkedList<Shift> list = new LinkedList<Shift>();

            while (rdr.Read())
            {
                string id = (string)rdr["id"];
                string start = (string)rdr["start"];
                string end = (string)rdr["end"];

                list.AddLast(new Shift(id, start, end));
            }
            connection.Close();
            return list;
        }

        public static void updateShift(Shift befor,Shift after)
        {
            if (!initiated)
                init();
            //UPDATE `whm1`.`shifts` SET `Start` = '3/8/2013::09:00',
            //`End` = '3/8/2013::16:00' WHERE `shifts`.`ID` = '021894266' AND `shifts`.`Start` = '1/9/2013::09:00';            
            string newCommand = "UPDATE shifts SET ";
            newCommand += "Start = '" + after.Start + "',";
            newCommand += "End = '" + after.End + "' ";
            newCommand += "WHERE ID = '" + befor.id + "' AND Start = '" + befor.Start + "';";
            
            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            //	Changed a shift on 1/8/2013::08:00 - 1/8/2013::16:00 to 1/8/2013::08:00 - 1/8/2013::17:00

            addToChangeLog(new ChangeLogEntry(0, befor.id, DAL.getEmployeeById(befor.id).Team, "Changed a shift from " + befor.Start + " - "+befor.End+" to "+after.Start+" - "+after.End));
        }
        public static void updateEmployee(Employee employee)
        {
            if (!initiated)
                init();

           
            string newCommand = "UPDATE workers SET ";
            newCommand += "ID = '" + employee.id + "',";
            newCommand += "firstName = '" + employee.firstName + "',";
            newCommand += "lastName = '" + employee.lastName + "', ";
            newCommand += "Password = '" + employee.Password + "', ";
            newCommand += "Position = '" + employee.Position + "', ";
            newCommand += "isManager = '" + employee.isManager + "', ";
            newCommand += "isAdmin = '" + employee.isAdmin + "', ";
            newCommand += "managerOf = '" + employee.managerOf + "', ";
            newCommand += "Team = '" + employee.Team + "', ";
            newCommand += "minHours = '" + employee.minHours + "'";
            newCommand += " WHERE ID = '" + employee.id + "';";
            
            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

            addToChangeLog(new ChangeLogEntry(0, "Sys Admin", "", "Updated the Employee: " + employee.id + " " + employee.firstName + " " + employee.lastName));

        }
        public static void changePassword(Employee emp, String newPass)
        {
            if (!initiated)
                init();

            //UPDATE `whm1`.`workers` SET `Password` = '123' WHERE `workers`.`ID` = '11111111';
            string newCommand = "UPDATE workers SET Password = '" + newPass + "' WHERE ID = '" + emp.id + "';";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();
        }

        public static void openConnection()
        {
            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();
        }

        //public static LinkedList<Shift> getAllShifts(string name)
        //{
        //    if (!initiated)
        //        init();

        //    string newCommand = "SELECT * FROM shifts";

        //    openConnection();
        //    MySqlCommand command = new MySqlCommand(newCommand, connection);
        //    MySqlDataReader rdr = command.ExecuteReader();

        //    LinkedList<Shift> list = new LinkedList<Shift>();

        //    while (rdr.Read())
        //    {
        //        string id = (string)rdr["id"];
        //        string start = (string)rdr["start"];
        //        string end = (string)rdr["end"];

        //        list.AddFirst(new Shift(id, start, end));
        //    }
        //    connection.Close();
        //    return list;

        //}

        public static bool checkOutOfWork(string id, string endTime)
        {
            try
            {
                if (!initiated)
                    init();
                // UPDATE `whm1`.`shifts` SET `End` = '9/8/2013::21:19' WHERE `shifts`.`ID` = '11111111' AND `shifts`.`Start` = '9/8/2013::20:19';
                string newCommand = "";
                newCommand += "UPDATE shifts SET " +
                    "End = '" + endTime + "'" +
                    "WHERE ID = '" + id + "'" +
                    "AND Start = '" + BL.getLastOpenShift(id) + "';";

                openConnection();
                MySqlCommand command = new MySqlCommand(newCommand, connection);
                MySqlDataReader rdr = command.ExecuteReader();
                connection.Close();

                addToChangeLog(new ChangeLogEntry(0, id, DAL.getEmployeeById(id).Team, id + " Has Existed Work at: " + endTime));

                return true;
            }
            catch
            {
                return false;
            }
            
        }

        public static List<Employee> getAllEmployeesByTeam(string team,Boolean isA)
        {
            if (!initiated)
                init();

               //SELECT *
               //FROM workers
               //WHERE Team = 'Blue'

                string newCommand = "";
                newCommand += "SELECT * " +
                              "FROM workers " +
                              "WHERE Team = '" + team + "';";

                if (isA)
                {
                    newCommand = "";
                    newCommand += "SELECT * " +
                                  "FROM workers;";
                }

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            

            List<Employee> list = new List<Employee>();

            while (rdr.Read())
            {
                string id = (string)rdr["id"];
                string firstName = (string)rdr["firstName"];
                string lastName = (string)rdr["lastName"];
                string Password = (string)rdr["Password"];
                string Position = (string)rdr["Position"];
                string isManager = (string)rdr["isManager"];
                string isAdmin = (string)rdr["isAdmin"];
                string managerOf = (string)rdr["managerOf"];
                string Team = (string)rdr["Team"];
                string minHours = (string)rdr["minHours"];

                list.Add(new Employee(id, firstName, lastName, Password, Position, isManager, isAdmin, managerOf, Team, minHours));
            }

            connection.Close();

            return list;

        }

        public static List<ChangeLogEntry> getChangeLog()
        {
            if (!initiated)
                init();

            string newCommand = "";
            newCommand += "SELECT * " +
                "FROM changeLog;";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            List<ChangeLogEntry> list = new List<ChangeLogEntry>();

            while (rdr.Read())
            {
                int changeLogId = (int)rdr["changeLogId"];
                string ID = (string)rdr["ID"];
                string team = (string)rdr["team"];
                string text = (string)rdr["text"];
                list.Add(new ChangeLogEntry(changeLogId,ID, team, text));
            }

            connection.Close();

            return list;
        }

        public static void addToChangeLog(ChangeLogEntry c)
        {
            //INSERT INTO `whm1`.`changeLog` (`changeLogId` ,`ID` ,`team` ,`text`)
            //VALUES (NULL , '304284383', 'Blue', 'Changed a shift on 1/8/2013::08:00 - 1/8/2013::16:00 to 1/8/2013::08:00 - 1/8/2013::17:00');
            string newCommand = "";
            newCommand += "INSERT INTO changeLog (changeLogId ,ID ,team ,text)" +
                " VALUES (NULL , '"+c.ID+"', '"+c.team+"', '"+c.text+"');";

            openConnection();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();

        }
    }
}